
package Server;

import java.sql.Date;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Vector;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
import com.sun.xml.internal.bind.v2.schemagen.xmlschema.List;

public class Searccomemploy {
	/*
	 * 
	 * this method return to the gui all complains added by the employ(the details taken from 
	 * userinfo (public static srting that hold all the info of user that now using the system)
	  
	 * @author Ariel
	 * @param con1 Connection to the database
	 * @param log Array of String which old . on log[1] the employ username
	 * @return  ArrayList  of Strings => each list is a row on the complaint table (that added by the employ on log[1]
	 *  
	 */
	public static ArrayList Searchcomemploy(Connection con1,String[] log )
	{		
		ArrayList a = new ArrayList<String[]>();		
		
		String query = null;		
		try {
			java.sql.Statement stmt = con1.createStatement();						
			
		
			java.sql.ResultSet res = stmt.executeQuery("SELECT * FROM complaint WHERE Employ= '" +log[1]+"'" );
			
			while (res.next()) {
				String[] temp = new String[7];//
					temp[0]=Integer.toString(res.getInt(1));	//id	
					temp[1]=res.getString(3);	//USERNAME
					temp[2]=res.getString(2);	//DESCRIPTION
					temp[3]=res.getString(4);	//EMPLOY NAME
					temp[4]=res.getString(5);	//COMPLAINT STATE
					temp[5]=res.getString(6);	//date
				// create iems's instance and insert to list
				a.add(temp);
			}
			res.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
			
		return a;	
	}
	
	/*
	 * 
	 * this method return to the gui all complains added for the user name that comes
	 * on log[1]
	 * i put it on array list and then on gui we can see it on table
	 * @author Ariel
	 * @param con1 Connection to the database
	 * @param log Array of String which old . on log[1] the client username
	 * @return  ArrayList  of Strings => each list is a row on the complaint table (that belongs to the client on log[1]
	 */
	public static ArrayList Searchccustomer(Connection con1,String[] log )
	{		
		ArrayList a = new ArrayList<String[]>();		
		
		String query = null;		
		try {
			java.sql.Statement stmt = con1.createStatement();						
			
		
		
				
			
			java.sql.ResultSet res = stmt.executeQuery("SELECT * FROM complaint WHERE User_name= '" +log[1]+"'" );
			
			while (res.next()) {
				String[] temp = new String[7];//
					temp[0]=Integer.toString(res.getInt(1));	//id	
					temp[1]=res.getString(3);	//USERNAME
					temp[2]=res.getString(2);	//DESCRIPTION
					temp[3]=res.getString(4);	//EMPLOY NAME
					temp[4]=res.getString(5);	//COMPLAINT STATE
					temp[5]=res.getString(6);	//date
				// create iems's instance and insert to list
				a.add(temp);
			}
			res.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
			
		return a;	
	}
	
	/*
	 * 
	 * the method return a id for the next complain by counting the complains in the data base.
	 * @author Ariel
	 * @param con1 Connection to the database
	 * @param non
	 * @return  ArrayList  of Strings on the Rownum[1]= the new comp id
	 */
	public static String[] getid(Connection con1 )
	{
	
		String[] RowNum=new String[2];
		int i=0;
		 try
         {
            java.sql.Statement stmt = con1.createStatement();          
            java.sql.ResultSet res = stmt.executeQuery("SELECT * FROM complaint");
 			while (res.next()) 
 				i++;
             
 			i++;
 			RowNum[0]=Integer.toString(i);
		
 			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	

		return RowNum;
		
	}



	/*
	 * 
	 * this method added money to the client account and close the complaint by turnning the complaint state 
	 * to close 
	 * on log[1] is the id of the complaint
	 * 
	 * @author Ariel
	 * @param con1 Connection to the database
	 * @param log[2]= the money we want to add to the client ,log[1]=the id of the complaint
	 * @return nothing
	 *  
	 */
	
	public static void moneyandclose(Connection con1,String[] log )
	{		
				int balanc=0;
				int addmoney=0;
				int idcom=0;
				String user=null;	
				String moneyadd=log[2];
				addmoney=Integer.parseInt(moneyadd);
				java.sql.ResultSet res2=null;
				java.sql.ResultSet res = null;
				java.sql.PreparedStatement ps=null;
				java.sql.PreparedStatement ps1=null;
				 		
			try {
				java.sql.Statement stmt = con1.createStatement();
				
				// execute SQL query to get all the book that match to the input
				
				idcom=Integer.parseInt(log[1]);
				
				res= stmt.executeQuery("SELECT * FROM complaint WHERE idc="+idcom);
					if (res.next()) 				
					{
						user=res.getString(2);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
										
			  try {
				  java.sql.Statement stmt1 = con1.createStatement();
			
				res2= stmt1.executeQuery("SELECT * FROM clients WHERE User_name ='"+user+"'");
				if (res2.next()) 				
				{
					balanc=res2.getInt(9);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
				
				  balanc=balanc+addmoney;
		
			  try {
				java.sql.Statement stmt2 = con1.createStatement();
					
				 
		           ps = con1.prepareStatement("UPDATE clients SET money="+balanc+" WHERE User_name='"+user+"'");
					ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
              try {
		
				  ps1 = con1.prepareStatement("UPDATE complaint SET compstate='closed' WHERE idc="+idcom);
					ps1.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
          

	}

	/*
	 * 
	 * this method added money to the client account   
	 * on log[1] is the id of the complaint
	 * @author Ariel
	 * @param con1 Connection to the database
	 * @param log[2]= the money we want to add to the client,log[1]=the id of the complaint 
	 * @return nothing
	 *   
	 */
	

public static void money(Connection con1,String[] log )
{		
			int balanc=0;
			int addmoney=0;
			int idcom=0;
			String user=null;	
			String moneyadd=log[2];
			addmoney=Integer.parseInt(moneyadd);
			java.sql.ResultSet res2=null;
			java.sql.ResultSet res = null;
			java.sql.PreparedStatement ps=null;
		
			 		
		try {
			java.sql.Statement stmt = con1.createStatement();
			
		
			
			idcom=Integer.parseInt(log[1]);
			
			res= stmt.executeQuery("SELECT * FROM complaint WHERE idc="+idcom);
				if (res.next()) 				
				{
					user=res.getString(2);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
									
		  try {
			  java.sql.Statement stmt1 = con1.createStatement();
		
			res2= stmt1.executeQuery("SELECT * FROM clients WHERE User_name ='"+user+"'");
			if (res2.next()) 				
			{
				balanc=res2.getInt(9);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			
			  balanc=balanc+addmoney;
	
		  try {
			java.sql.Statement stmt2 = con1.createStatement();
				
			 
	           ps = con1.prepareStatement("UPDATE clients SET money="+balanc+" WHERE User_name='"+user+"'");
				ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
       
      

}

/*
 * 
 * this method close the complaint by turning the complaint state 
 * to close 
 * on log[1] is the id of the complaint
 * @author Ariel
 * @param con1 Connection to the database
 * @param log[1]=the id of the complaint we want to close 
 * @return nothing 
 */
public static void closecomp(Connection con1,String[] log )
{		
	

	int idcom=0;

	
	
	
	

	java.sql.PreparedStatement ps1=null;
	 		


  try {
	  idcom=Integer.parseInt(log[1]);
	  ps1 = con1.prepareStatement("UPDATE complaint SET compstate='closed' WHERE idc="+idcom);
		ps1.executeUpdate();
} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
}


}

}


